#region Copyright (c) 2006-2009 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2009 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Data;
using System.Data.SqlClient;

namespace Widgetsphere.Generator
{
	/// <summary>
	/// Summary description for DbHelper.
	/// </summary>
	public class DatabaseHelper
	{
		private DatabaseHelper()
		{}

		#region SQL

		public static DataSet ExecuteDataset(string connectionString, string sql)
		{
			DataSet retVal = new DataSet();

			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand();
			command.CommandType = CommandType.Text;
			command.CommandText = sql;
			command.Connection = connection;
			command.CommandTimeout = 300;
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = (SqlCommand)command;

			try
			{
				da.Fill(retVal);
				connection.Open();
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return retVal;
		}

		[Obsolete("This should not be used in new code", false)]
		public static DataSet ExecuteDataset(string connectionString, string storedProcedureName, SqlParameter []  arParms)
		{
			DataSet retVal = new DataSet();

			IDbConnection connection =  GetConnection(connectionString);
			IDbCommand command = GetCommand();
			foreach(SqlParameter param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = (SqlCommand)command;

			try
			{
				da.Fill(retVal);
				connection.Open();
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex);
			}
			finally
			{
				if(connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return retVal;
		}

		[Obsolete("This should not be used in new code", false)]
		public static void ExecuteNonQuery(string connectionString, string storedProcedureName, SqlParameter[] arParms)
		{
			
			IDbConnection connection =  GetConnection(connectionString);
			IDbCommand command = GetCommand();
			foreach(SqlParameter param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;
			try
			{
				connection.Open();
				command.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.ToString());
			}
			finally
			{
				if(connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
		
		}

		public static int ExecuteNonQuery(IDbCommand command)
		{
			SqlCommand cmd = (SqlCommand)command;
			return cmd.ExecuteNonQuery();
		}

    //private static SqlConnection sqlConnection = null;
		public static IDbConnection GetConnection(string connectionString)
		{
      //if (sqlConnection == null)
      //{
      SqlConnection sqlConnection = new SqlConnection();
      sqlConnection.ConnectionString = connectionString;			
      //}
      return sqlConnection;
		}
		
		public static IDbCommand GetCommand(string connectionString, string spName)
		{
			return null;
			//StoredProcedure sp = StoredProcedureFactory.GetStoredProcedure(connectionString, spName);
			//return sp.GetDirectQueryCommand();
		}

		public static void SetParameterValue(IDbCommand command, string paramName, object inValue)
		{
			try
			{
				IDbDataParameter currentParam = (IDbDataParameter)command.Parameters[paramName];
				if (currentParam != null)
				{
					currentParam.Value = inValue;
				}
				else
				{
					throw new Exception("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText); 
				}
			}
			catch (Exception ex)
			{
				throw new Exception("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText, ex); 
			}
		}

    internal static IDataReader ExecuteReader(IDbConnection connection, CommandType cmdType, string stmt)
    {
      IDbCommand cmd = GetCommand();
      cmd.CommandText = stmt;
      cmd.CommandType = cmdType;
			cmd.CommandTimeout = 300;

      if (connection.State == ConnectionState.Closed)
        connection.Open();

      cmd.Connection = connection;      
      IDataReader dr = cmd.ExecuteReader();
      return dr;
    }

		internal static IDataReader ExecuteReader(string connectString, CommandType cmdType, string stmt)
		{
      try
      {
        IDbConnection connection = GetConnection(connectString);
        connection.Open();
        return ExecuteReader(connection, cmdType, stmt);
      }
      catch (Exception ex)
      {
        throw;
      }
		}

		internal static IDataReader ExecuteReader(IDbCommand selectCommand)
		{ 
			SqlCommand cmd = (SqlCommand)selectCommand;
			IDataReader dr = cmd.ExecuteReader();
			return dr;
		}

		internal static IDbDataAdapter GetDataAdapter()
		{
			SqlDataAdapter da = new SqlDataAdapter();
			return da;
		}
    
		internal static IDbCommand GetCommand()
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			return cmd;
		}

		internal static void Fill(IDbDataAdapter da, DataTable dt)
		{
			SqlDataAdapter sqlDa = (SqlDataAdapter)da;
			sqlDa.Fill(dt);
		}

		internal static void Update(IDbDataAdapter da, DataTable dt)
		{
			SqlDataAdapter sqlDa = (SqlDataAdapter)da;
			if (dt != null && dt.Rows.Count > 0)
			{
				try
				{
					sqlDa.Update(dt);
				}
				catch(Exception ex)
				{
					Console.Write(ex.ToString());
					throw ex;
				}
			}
		}
		internal static void Update(IDbDataAdapter da, DataRow[] rows)
		{
			SqlDataAdapter sqlDa = (SqlDataAdapter)da;
			if (rows.Length > 0)
			{
				try
				{
					sqlDa.Update(rows);
				}
				catch(Exception ex)
				{
					Console.Write(ex.ToString());
					throw ex;
				}
			}
		}
		#endregion
		
		internal static void SetTransaction(IDbDataAdapter da, IDbTransaction trans)
		{
			if (da.InsertCommand != null)
			{
				da.InsertCommand.Transaction = trans;
			}
			if (da.SelectCommand != null)
			{
				da.SelectCommand.Transaction = trans;
			}
			if (da.UpdateCommand != null)
			{
				da.UpdateCommand.Transaction = trans;
			}
			if (da.DeleteCommand != null)
			{
				da.DeleteCommand.Transaction = trans;
			}
		}

		internal static void SetConnections(IDbDataAdapter da, IDbConnection conn)
		{
			if (da.InsertCommand != null)
			{
				da.InsertCommand.Connection = conn;
			}
			if (da.SelectCommand != null)
			{
				da.SelectCommand.Connection = conn;
			}
			if (da.UpdateCommand != null)
			{
				da.UpdateCommand.Connection = conn;
			}
			if (da.DeleteCommand != null)
			{
				da.DeleteCommand.Connection = conn;
			}
		}
	}
}
